import time
import pymysql

def get_time():
    time_str = time.strftime("%Y{}%m{}%d{} %X")
    return time_str.format("年","月","日")


def get_conn():
    # 建立连接
    conn = pymysql.connect(host="localhost", port=3306, user="root", password="123456", db="cov", charset="utf8")
    # c创建游标A
    cursor = conn.cursor()
    return conn, cursor


def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()

def query(sql,*args):
    """

    :param sql:
    :param args:
    :return:
    """
    conn,cursor = get_conn()
    cursor.execute(sql,args)
    res = cursor.fetchall()
    close_conn(conn,cursor)
    return res

def test():
    sql = "select * from map"
    res = query(sql)
    return res

def get_c1_data():
    sql = "select sum(confirm)," \
          "sum(heal),sum(dead) from details " \
          "where update_time=(select update_time from details order by update_time desc limit 1) "
    res = query(sql)
    return res[0]

def get_c2_data():
    sql = "select province,total_confirmed from map " \
          "group by province"
    res = query(sql)
    return res

def get_l1_data():
    sql = "select ds,confirm,suspect,heal,dead from history"
    res = query(sql)
    return res

def get_l2_data():
    sql = "SELECT * FROM confirmed_gobal " \
          "WHERE Country = 'China'"
    res = query(sql)
    L = len(res[0])
    data_c = [0]*(L-4)
    for i in range(len(res)):
        for j in range(4,L):
            data_c[j-4] = data_c[j-4]+res[i][j]
    sql = "SELECT * FROM recovered_global " \
          "WHERE Country = 'China'"
    res = query(sql)
    L = len(res[0])
    data_r = [0] * (L - 4)
    for i in range(len(res)):
        for j in range(4, L):
            data_r[j - 4] = data_r[j - 4] + res[i][j]
    sql = "SELECT * FROM deaths_gobal " \
          "WHERE Country = 'China'"
    res = query(sql)
    L = len(res[0])
    data_d = [0] * (L - 4)
    for i in range(len(res)):
        for j in range(4, L):
            data_d[j - 4] = data_d[j - 4] + res[i][j]
    return [data_c,data_r,data_d]

def get_r1_data():
    sql = "select province,total_confirmed from map " \
          "order by total_confirmed desc limit 5"
    res = query(sql)
    return res

def get_r2_data():
    sql = "select ds,confirm_add,suspect_add from history"
    res = query(sql)
    return res

if __name__ == "__main__":
    print(get_c1_data())
    # print(test())